﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;


namespace MolyMart
{
    class SQLites:IDisposable
    {
        private readonly string _dbPath;
        private readonly string _dbstring;
        private SQLiteConnection _sqlConnection;
        private readonly List<ServerValuesItem> _valueItems = new List<ServerValuesItem>();
        public int ItemCount = 0;
        private List<string> tableList = new List<string>();


        public SQLites()
        {
            _dbPath = $"{DateTime.Now.Year.ToString("D4")}{DateTime.Now.Month.ToString("D2")}{DateTime.Now.Day.ToString("D2")}" +
                      $"{DateTime.Now.Hour.ToString("D2")}{DateTime.Now.Minute.ToString("D2")}{DateTime.Now.Second.ToString("D2")}.archive";
            _dbstring = "Data Source = " + _dbPath; // "Data Source = 20160628083455.archive"
            CreateDb();
        }

        private void CreateDb()
        {
            _sqlConnection = new SQLiteConnection(_dbstring);
            _sqlConnection.Open();
        }

        public void CommitItems()
        {
            using (SQLiteTransaction transaction = _sqlConnection.BeginTransaction())
            {
                foreach (ServerValuesItem item in _valueItems)
                {
                    if (!tableList.Contains(item.ServerId))
                    {
                        string x = string.Join(",",item.Values.Select(i=> { return $"{i.Name} text"; }));
                        SQLiteCommand command0 = new SQLiteCommand(
                             $@"CREATE TABLE IF NOT EXISTS {item.ServerId}" +
                             $@"({x});", _sqlConnection);
                        command0.ExecuteNonQuery();
                        tableList.Add(item.ServerId);
                    }

                    SQLiteCommand command = new SQLiteCommand(_sqlConnection)
                    {
                        Transaction = transaction,
                        CommandText = "INSERT INTO CNC_States VALUES (@id,@Name,@KeyId,@Value,@Quality,@TimeStamp,@Format)"
                    };
                    command.Parameters.AddRange(new SQLiteParameter[]
                    {
                            new SQLiteParameter("@id",Utils.GetUnixTimeStamp()),
                            new SQLiteParameter("@Name",item.ServerId),
                            new SQLiteParameter("@Key",Utils.GetUnixTimeStamp()),


                    });
                    command.ExecuteNonQuery();
                }
                _valueItems.Clear();
                transaction.Commit();
            }
        }

        public void InsertItem(ServerValuesItem item)
        {
            _valueItems.Add(item);
            ItemCount = _valueItems.Count;
        }


        public void Dispose() //主动调用
        {
            _sqlConnection.Close();
            _sqlConnection.Dispose();
        }
    }
}
